﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsInvoiceManageDAL.cs
/// 作  者：黄纲
/// 时  间：2006.12.04
/// 说  明：ClsInvoiceManageDAL类用于发票管理信息相关处理
/// </summary>
public class ClsInvoiceManageDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取发票数据集
    /// </summary>
    /// <param name="S_Type"></param>
    /// <param name="Param">查询参数</param>
    /// <param name="UserID">当前登陆用户ID</param>
    /// <param name="QueryMode">查询方式(0为精确，1为模糊)</param>
    /// <param name="Type">返回数据集类型(0全部,1确认过的,2未确认的)</param>
    /// <returns>返回数据集</returns>
    public DataSet GetInvoiceDataSet(int S_Type, string Param, string UserID, int QueryMode, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetInvoiceDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@s_type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@s_type"].Value = S_Type;
        dat.SelectCommand.Parameters.Add("@param", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@param"].Value = Param;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@mode", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@mode"].Value = QueryMode;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取发票从表信息
    /// </summary>
    /// <param name="S_Type"></param>
    /// <param name="S_No"></param>
    /// <returns></returns>
    public DataSet GetInvoiceDataSetChild(int S_Type, string S_No)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetInvoiceDataSetChild", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@s_type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@s_type"].Value = S_Type;
        dat.SelectCommand.Parameters.Add("@s_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@s_no"].Value = S_No;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 添加或修改发票
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="Type"></param>
    /// <param name="I_No"></param>
    /// <param name="I_Date"></param>
    /// <param name="Tax_No"></param>
    /// <param name="Inv_No"></param>
    /// <param name="No_Type"></param>
    /// <param name="Out_No"></param>
    /// <param name="Oper"></param>
    /// <param name="Remark"></param>
    /// <returns></returns>
    public Boolean AddOrUpdateInvoice(int ID, int Type, string I_No, DateTime I_Date, string Tax_No, string Inv_No, int No_Type, string Out_No, string Oper, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateInvoice", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;
        com.Parameters.Add("@i_no", SqlDbType.NVarChar, 50);
        com.Parameters["@i_no"].Value = I_No;
        com.Parameters.Add("@i_date", SqlDbType.DateTime, 8);
        com.Parameters["@i_date"].Value = I_Date;
        com.Parameters.Add("@tax_no", SqlDbType.NVarChar, 50);
        com.Parameters["@tax_no"].Value = Tax_No;
        com.Parameters.Add("@inv_no", SqlDbType.NVarChar, 50);
        com.Parameters["@inv_no"].Value = Inv_No;
        com.Parameters.Add("@i_type", SqlDbType.Int, 4);
        com.Parameters["@i_type"].Value = No_Type;
        com.Parameters.Add("@up_no", SqlDbType.NVarChar, 50);
        com.Parameters["@up_no"].Value = Out_No;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 50);
        com.Parameters["@oper"].Value = Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改从表记录
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="Price"></param>
    /// <param name="Money"></param>
    /// <param name="Agio"></param>
    /// <param name="Remark"></param>
    /// <returns></returns>
    public Boolean UpdateInvoiceInfoChild(int ID, decimal Price, decimal Money, decimal Agio, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateInvoiceInfoChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@price", SqlDbType.Decimal, 28);
        com.Parameters["@price"].Value = Price;
        com.Parameters.Add("@money", SqlDbType.Decimal, 28);
        com.Parameters["@money"].Value = Money;
        com.Parameters.Add("@agio", SqlDbType.Decimal, 28);
        com.Parameters["@agio"].Value = Agio;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改销售发票从表记录
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="Type"></param>
    /// <param name="Agio"></param>
    /// <param name="Remark"></param>
    /// <returns></returns>
    public Boolean UpdateSellInvoiceChild(int ID, int Type, decimal Agio, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellInvoiceChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;
        com.Parameters.Add("@agio", SqlDbType.Decimal, 28);
        com.Parameters["@agio"].Value = Agio;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废发票信息
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="State"></param>
    /// <returns></returns>
    public Boolean DeleteInvoiceInfo(int ID, int State)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteInvoiceInfo", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = State;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废发票从表信息
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="Type"></param>
    /// <returns></returns>
    public Boolean DeleteInvoiceChild(int ID, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteInvoiceChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Int, 4);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 确认发票主单
    /// </summary>
    /// <param name="S_Type">类型</param>
    /// <param name="M_No">出库单号</param>
    /// <param name="N_No">开票申请单号</param>
    /// <param name="Auditor">确认人</param>
    /// <returns></returns>
    public int AuditInvoice(int S_Type, string M_No, string N_No, string Auditor)
    {
        string strPro_Name = (S_Type == 0) ? "Pro_AuditInvoice" : "Pro_AuditSellInvoice";

        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand(strPro_Name, con);
        SqlParameter PrmOutPut = new SqlParameter();

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_type", SqlDbType.Int, 4);
        com.Parameters["@s_type"].Value = S_Type;
        com.Parameters.Add("@m_no", SqlDbType.NVarChar, 50);
        com.Parameters["@m_no"].Value = M_No;
        com.Parameters.Add("@n_no", SqlDbType.NVarChar, 50);
        com.Parameters["@n_no"].Value = N_No;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 50);
        com.Parameters["@auditor"].Value = Auditor;
        PrmOutPut = com.Parameters.Add("@errcount", SqlDbType.Int, 4);
        PrmOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return Int32.Parse(com.Parameters["@errcount"].Value.ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 取消确认发票主单
    /// </summary>
    /// <param name="S_Type">类型</param>
    /// <param name="M_No">出库单号</param>
    /// <param name="N_No">开票申请单号</param>
    /// <returns></returns>
    public Boolean OAuditInvoice(int S_Type, string M_No, string N_No)
    {
        string strPro_Name = (S_Type == 0) ? "Pro_OAuditInvoice" : "Pro_OAuditSellInvoice";

        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand(strPro_Name, con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@s_type", SqlDbType.Int, 4);
        com.Parameters["@s_type"].Value = S_Type;
        com.Parameters.Add("@m_no", SqlDbType.NVarChar, 50);
        com.Parameters["@m_no"].Value = M_No;
        com.Parameters.Add("@n_no", SqlDbType.NVarChar, 50);
        com.Parameters["@n_no"].Value = N_No;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取未开票的金额
    /// </summary>
    /// <param name="Dm_No"></param>
    /// <param name="Im_No"></param>
    /// <returns></returns>
    public decimal GetInvoiceChildRsSpareMoney(string No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetInvoiceChildRsSpareMoney", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@no", SqlDbType.NVarChar, 50);
        com.Parameters["@no"].Value = No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 50);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return decimal.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return 0;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断开票申请是否存在于销售发票|
    /// 判断销售发票是否存在于销售收款
    /// </summary>
    /// <param name="No"></param>
    /// <param name="Type"></param>
    /// <returns></returns>
    public int InvoiceExistState(string No, int Type)
    {
        string strPro_Name = (Type == 0) ? "Pro_SellInvoiceExistStat" : "Pro_InvoiceExistStat";

        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand(strPro_Name, con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@i_no", SqlDbType.NVarChar, 50);
        com.Parameters["@i_no"].Value = No;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
}
